package com.max.mysql.dbutils;

import com.max.mysql.utils.DroiJob;
import com.max.mysql.utils.JDBCPoolUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

public class DbutilsDemo {

    public static void main(String[] args) throws SQLException {
//        insert();
//        update();
//        delete();
//        queryOne();
//        queryAll();

        // 可能会用
        // one2JavaBean();
        // mul2JavaBeanList();
//        one2Map();
//        mul2Map();

        //以下不常用
//        one2Array();
//        mul2List();
//        column2List();
//        scalarHandler();
//        keyedHandler();
    }


    /**
     * 添加操作
     */
    public static void insert() throws SQLException {
        // 创建核心类：QueryRunner:
        QueryRunner queryRunner = new QueryRunner(JDBCPoolUtils.getDataSource());
        queryRunner.update("insert into droi_emp(ename, password) values (?,?)", "dbutils", "123321");
    }

    /**
     * 修改操作
     */
    public static void update() throws SQLException{
        // 创建核心类:
        QueryRunner queryRunner = new QueryRunner(JDBCPoolUtils.getDataSource());
        queryRunner.update("update droi_emp set ename = ? where emp_no =?", "990", 990);
    }

    /**
     * 删除操作
     */
    public static void delete() throws SQLException{
        // 创建核心类:
        QueryRunner queryRunner = new QueryRunner(JDBCPoolUtils.getDataSource());
        queryRunner.update("delete from droi_emp where emp_no = ?", 990);
    }

    /**
     * 查询一条记录的操作
     */
    public static void queryOne() throws SQLException{
        // 创建核心类:
        QueryRunner queryRunner = new QueryRunner(JDBCPoolUtils.getDataSource());
        // 执行查询:
        String sql = "select * from droi_job where job_id = ?";
        DroiJob job = queryRunner.query(sql, new ResultSetHandler<DroiJob>() {
            @Override
            public DroiJob handle(ResultSet resultSet) throws SQLException {
                DroiJob droiJob = new DroiJob();
                if (resultSet.next()) {
                    droiJob.setJobId(resultSet.getInt("job_id"));
                    droiJob.setJobName(resultSet.getString("job_name"));
                    droiJob.setJobDesc(resultSet.getString("job_desc"));
                }
                return droiJob;
            }
        }, 1);
        System.out.println(job);
    }

    /**
     * 查询多条记录
     */
    public static void queryAll() throws SQLException{
        // 创建核心类:
        QueryRunner queryRunner = new QueryRunner(JDBCPoolUtils.getDataSource());
        // 执行查询:
        String sql = "select * from droi_job";

        // 执行查询:
        List<DroiJob> list = queryRunner.query(sql, new ResultSetHandler<List<DroiJob>>(){
            @Override
            public List<DroiJob> handle(ResultSet rs) throws SQLException {
                // 创建一个集合用于封装数据:
                List<DroiJob> list = new ArrayList<DroiJob>();
                while(rs.next()){
                    DroiJob droiJob = new DroiJob();
                    droiJob.setJobId(rs.getInt("job_id"));
                    droiJob.setJobName(rs.getString("job_name"));
                    droiJob.setJobDesc(rs.getString("job_desc"));
                    list.add(droiJob);
                }
                return list;
            }
        });

        // 查询列表
        for (DroiJob droiJob : list) {
            System.out.println(droiJob);
        }
    }


    /**
     * ArrayHandler:将一条记录封装到一个Object数组中
     */
    public static void one2Array() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JDBCPoolUtils.getDataSource());
        Object[] objs = queryRunner.query("select * from droi_emp where emp_no = ?", new ArrayHandler(), 1);
        System.out.println(Arrays.toString(objs));
    }

    /**
     * ArrayListHandler:将多条记录封装到一个装有Object数组的List集合中 *
     * 一条记录封装到Objecct[]数组中，多条记录就是多个Object[]，那么多个Object数组就将其装入List集合中即可。
     */
    public static void mul2List() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JDBCPoolUtils.getDataSource());
        List<Object[]> list = queryRunner.query("select * from droi_emp", new ArrayListHandler());
        for (Object[] objects : list) {
            System.out.println(Arrays.toString(objects));
        }
    }

    /**
     * BeanHandler：将一条记录封装到一个JavaBean中
     */
    public static void one2JavaBean() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JDBCPoolUtils.getDataSource());
        DroiJob account = queryRunner.query("select * from droi_emp where emp_no = ?",
                new BeanHandler<DroiJob>(DroiJob.class), 2);
        System.out.println(account);
    }

    /**
     * BeanListHandler:将多条记录封装到一个装有JavaBean的List集合中。 
     * *一条记录就是一个Java的对象（JavaBean），如果多条记录（多个Java的对象），将多个Java对象装到一个List集合中。
     */
    public static void mul2JavaBeanList() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JDBCPoolUtils.getDataSource());
        List<DroiJob> list = queryRunner.query("select * from droi_job", new BeanListHandler<DroiJob>(DroiJob.class));
        for (DroiJob account : list) {
            System.out.println(account);
        }
    }

    /**
     * MapHandler：将一条记录封装到一个Map集合中，Map的key是列名，Map的value就是表中列的记录值。
     */
    public static void one2Map() throws SQLException{
        QueryRunner queryRunner = new QueryRunner(JDBCPoolUtils.getDataSource());
        Map<String,Object> map = queryRunner.query("select * from droi_emp where emp_no = ?", new MapHandler(), 4);
        System.out.println(map);
    }

    /**
     * MapListHandler：将多条记录封装到一个装有Map的List集合中。
     */
    public static void mul2Map() throws SQLException{
        QueryRunner queryRunner = new QueryRunner(JDBCPoolUtils.getDataSource());
        List<Map<String,Object>> list = queryRunner.query("select * from droi_emp", new MapListHandler());
        for (Map<String, Object> map : list) {
            System.out.println(map);
        }
    }

    /**
     * ColumnListHandler：将某列的值封装到List集合中
     */
    public static void column2List() throws SQLException{
        QueryRunner queryRunner = new QueryRunner(JDBCPoolUtils.getDataSource());
        List<Object> list = queryRunner.query("select * from droi_emp", new ColumnListHandler("emp_no"));
        for (Object object : list) {
            System.out.println(object);
        }
    }

    /**
     * ScalarHandler:单值封装
     */
    public static void scalarHandler() throws SQLException{
        QueryRunner queryRunner = new QueryRunner(JDBCPoolUtils.getDataSource());
        Object obj = queryRunner.query("select count(*) from droi_emp", new ScalarHandler());
        System.out.println(obj);
    }

    /**
     * KeyedHandler：将一条记录封装到一个Map集合中。将多条记录封装到一个装有Map集合的Map集合中。而且外面的Map的key是可以指定的。
     */
    public static void keyedHandler() throws SQLException{
        QueryRunner queryRunner = new QueryRunner(JDBCPoolUtils.getDataSource());
        Map<Object,Map<String,Object>> map = queryRunner.query("select * from droi_emp", new KeyedHandler("emp_no"));
        for (Object key : map.keySet()) {
            System.out.println(key+" "+map.get(key));
        }
    }
}
